package cn.ffcs.bpmpvc.web;

import java.io.File;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.echoice.Constants;
import org.echoice.paper.PageBean;
import org.echoice.paper.PageNavi;
import cn.ffcs.base.web.BaseAction;
import cn.ffcs.bpm.model.ProcTranRecord;
import cn.ffcs.bpm.model.ProcessConfig;
import cn.ffcs.bpm.model.ProcessConfigDetail;
import cn.ffcs.bpm.model.TransactProcessStatus;
import cn.ffcs.bpmpvc.dao.AppBusinessReqDao;
import cn.ffcs.bpmpvc.dao.FlowPvcDao;
import cn.ffcs.bpmpvc.services.AppBusinessReqService;
import cn.ffcs.bpmpvc.services.AppDataTranferService;
import cn.ffcs.bpmpvc.svc.ProcessPvcService;
import cn.ffcs.bpmpvc.svc.TaskService;
import cn.ffcs.bpmpvc.web.forms.FlowReqReportForm;
import cn.ffcs.businessrequest.model.BusinessRequest;
import cn.ffcs.businessrequest.model.ReqProgress;
import cn.ffcs.util.context.ApplicationContextUtil;
import cn.ffcs.util.converter.DateUtil;

public class FlowReqReportAction<D extends TransactProcessStatus> extends
		BaseAction<D> {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private ProcessPvcService processPvcService;
	private TaskService taskService;
	private AppBusinessReqService appBusinessReqService;
	private FlowReqReportForm flowReqReportForm;
	private List<ProcessConfig> processList;
	private AppDataTranferService appDataTranferService;
	
	/**
	 * 需求单统计(分为按类型统计和按部门简称统计)
	 * @return
	 */
	public String sumRequest(){
		HttpServletRequest request = ApplicationContextUtil.getHttpServletRequest();
		if(flowReqReportForm==null){
			 flowReqReportForm=new FlowReqReportForm();
			 flowReqReportForm.setType("type");
		}
		List<List<String>> sumList=null;
		sumList=getAppBusinessReqService().getAppBusinessReqDao().listSumRequest(flowReqReportForm);
		request.setAttribute("sumList", sumList);
		return "sumRequest";
	}
	/**
	 * 导出需求单统计(分为按类型统计和按部门简称统计)
	 * @return
	 */
	public String outSum(){
		HttpServletResponse response = ApplicationContextUtil.getHttpServletResponse();
		if(flowReqReportForm==null){
			 flowReqReportForm=new FlowReqReportForm();
			 flowReqReportForm.setType("type");
		}
		List<List<String>> list=null;
		list=getAppBusinessReqService().getAppBusinessReqDao().listSumRequest(flowReqReportForm);
		
		try {
			// open file.
			// String load=request.getRealPath("tmp");
			response.setContentType("application/stream;charset=UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ URLEncoder.encode("需求单统计"+DateUtil.dateToStr(new Date(), "yyyy-MM-dd")+".xls", "UTF-8") + "\"");
			OutputStream os = response.getOutputStream();
			WritableWorkbook book = Workbook.createWorkbook(os);

			String[] headName = new String[] { "类型/部门", "完成", "未完成", "合计"};
			WritableSheet sheet = book.createSheet("Sheet_1", 0);
			WritableCellFormat cellFormat=new WritableCellFormat();
		    cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
			for (int i = 0; i < headName.length; i++) {
				Label label = new Label(i, 0, headName[i]);
				sheet.addCell(label);
			}
			Label label = null;
			List<String> strList=null;
			if (list != null && list.size() != 0) {
				for (int i = 0; i < list.size(); i++) {
					int j = 0;
					strList = (List<String>) list.get(i);
					label = new Label(j++, i + 1, strList.get(0));
					sheet.addCell(label);
					label = new Label(j++, i + 1, strList.get(1));
					label.setCellFormat(cellFormat);
					sheet.addCell(label);
					label = new Label(j++, i + 1, strList.get(2));
					label.setCellFormat(cellFormat);
					sheet.addCell(label);
					label = new Label(j++, i + 1, strList.get(3));
					label.setCellFormat(cellFormat);
					sheet.addCell(label);
				}
			}
			book.write();
			book.close();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 导出需求单报表，带图形
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String exportReportExcel() {
		HttpServletRequest request = ApplicationContextUtil.getHttpServletRequest();
		HttpServletResponse response = ApplicationContextUtil.getHttpServletResponse();
		FlowPvcDao flowPvcDao = getProcessPvcService().getFlowPvcDao();
		PageBean pageBean = getAppBusinessReqService().getAppBusinessReqDao()
				.queryPageBsReqReportList(flowReqReportForm,
						PageNavUtil.getPageNo(request), 20000);
		List<BusinessRequest> bsReqList = pageBean.getDataList();
		request.setAttribute("reportList", bsReqList);
		List<ProcessConfigDetail> processConfigDetails = flowPvcDao
				.findAllProcessDetail(Integer.parseInt(flowReqReportForm
						.getProcessId()));
		request.setAttribute("processConfigDetails", processConfigDetails);
		request.setAttribute("num", processConfigDetails.size());
		out(request, response);
		return null;
	}
	@SuppressWarnings("unchecked")
	public void out(HttpServletRequest request, HttpServletResponse response) {
		try {
			// open file.
			// String load=request.getRealPath("tmp");
			response.setContentType("application/stream;charset=UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ URLEncoder.encode("需求单报表"+DateUtil.dateToStr(new Date(), "yyyy-MM-dd")+".xls", "UTF-8") + "\"");
			OutputStream os = response.getOutputStream();
			WritableWorkbook book = Workbook.createWorkbook(os);
			List<BusinessRequest> list = (List<BusinessRequest>) request
					.getAttribute("reportList");
			List<ProcessConfigDetail> processConfigDetails = (List<ProcessConfigDetail>) request
					.getAttribute("processConfigDetails");
			String[] headName = new String[] { "需求名称", "重要性", "需求类别", "需求部门",
					"需求人", "产品部门", "产品经理", "提出时间", "关联产品" };
			WritableSheet sheet = book.createSheet("Sheet_1", 0);
			for (int i = 0; i < headName.length; i++) {
				Label label = new Label(i, 0, headName[i]);
				sheet.addCell(label);
			}
			int l = headName.length;
			for (int i = 0; i < processConfigDetails.size(); i++) {
				ProcessConfigDetail processDetail = processConfigDetails.get(i);
				Label label = new Label(l + i, 0, processDetail.getName());
				sheet.addCell(label);
			}
			// String path = request.getRealPath("images/new/jiantou.png");
			String path = Constants.SERVER_PATH + "/images/new/jiantou.png";
			File file = new File(path);// + "/jiantou.png"
			Label label = null;
			Date date = null;
			Integer num = 0;
			BusinessRequest bsRequest = null;
			for (int i = 0; i < list.size(); i++) {
				int j = 0;
				bsRequest = (BusinessRequest) list.get(i);
				label = new Label(j++, i + 1, bsRequest.getName());
				sheet.addCell(label);
				label = new Label(j++, i + 1, bsRequest.getImportance());
				sheet.addCell(label);
				label = new Label(j++, i + 1, bsRequest.getType());
				sheet.addCell(label);
				label = new Label(j++, i + 1, bsRequest.getStartDept());
				sheet.addCell(label);
				label = new Label(j++, i + 1, bsRequest.getStartPerson());
				sheet.addCell(label);
				label = new Label(j++, i + 1, bsRequest.getProdDept().getName());
				sheet.addCell(label);
				label = new Label(j++, i + 1, bsRequest.getProdPerson().getName());
				sheet.addCell(label);
				date = bsRequest.getAdvanTime();
				if (date == null) {
					label = new Label(j++, i + 1, "");
					sheet.addCell(label);
				} else {
					label = new Label(j++, i + 1, DateUtil.dateToStr(date,"yyyy-MM-dd"));
					sheet.addCell(label);
				}
				label = new Label(j++, i + 1, "");
				sheet.addCell(label);
				num = getProcessPvcService().getFlowPvcDao().getMaxOrderBy(
						bsRequest.getProcinstId());
				if (num == null) {
					num = 19;
				}
				WritableImage image = new WritableImage(j++, i + 1, num, 1,	file);
				sheet.addImage(image);
				sheet.setRowView(0, 450);
				for (int k = 0; k < j - 1; k++) {
					sheet.setColumnView(k, 25);
				}
			}
			book.write();
			book.close();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 导出需求单报表，非图形
	 */
	@SuppressWarnings("unchecked")
	public String exportReportExcelNotGrap() {
		HttpServletRequest request = ApplicationContextUtil.getHttpServletRequest();
		HttpServletResponse response = ApplicationContextUtil.getHttpServletResponse();
		findBsReqReportNotGraph(20000);
		outNotGraph(request, response);
		return null;
	}
	/**
	 * 导出excel,带图形
	 * @param request
	 * @param response
	 */
	@SuppressWarnings("unchecked")
	public void outNotGraph(HttpServletRequest request, HttpServletResponse response) {
		try {
			response.setContentType("application/stream;charset=UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ URLEncoder.encode("需求单报表"+DateUtil.dateToStr(new Date(), "yyyy-MM-dd")+".xls", "UTF-8") + "\"");
			OutputStream os = response.getOutputStream();
			WritableWorkbook book = Workbook.createWorkbook(os);
			List<BusinessRequest> list = (List<BusinessRequest>) request.getAttribute("reportList");
			String[] headName = new String[] { "需求单号", "产品经理", "需求名称", "需求内容",
					"提出时间", "完成时间", "当前节点", "当前办理人", "完成情况" };
			WritableSheet sheet = book.createSheet("Sheet_1", 0);
			for (int i = 0; i < headName.length; i++) {
				Label label = new Label(i, 0, headName[i]);
				sheet.addCell(label);
			}
			Label label = null;
			BusinessRequest bsRequest = null;
			ReqProgress progress=null;
			String taskIds="";
			for (int i = 0; i < list.size(); i++) {
				int j = 0;
				bsRequest = (BusinessRequest) list.get(i);
				label = new Label(j++, i + 1, bsRequest.getReqCode());
				sheet.addCell(label);
				label = new Label(j++, i + 1, bsRequest.getProdPerson().getName());
				sheet.addCell(label);
				label = new Label(j++, i + 1, bsRequest.getName());
				sheet.addCell(label);
				label = new Label(j++, i + 1, bsRequest.getContent());
				sheet.addCell(label);
				label = new Label(j++, i + 1, DateUtil.dateToStr(bsRequest.getBegin(),"yyyy-MM-dd"));
				sheet.addCell(label);
				if(bsRequest.getPlanTime()!=null){
					label = new Label(j++, i + 1, DateUtil.dateToStr(bsRequest.getPlanTime(),"yyyy-MM-dd"));
				}else{
					label = new Label(j++, i + 1, "");
				}				
				sheet.addCell(label);
				label = new Label(j++, i + 1, bsRequest.getCurrStatus());
				sheet.addCell(label);
				label = new Label(j++, i + 1, bsRequest.getCurrUser());
				sheet.addCell(label);
//				<s:iterator value="reqProgresses" status="rp">
//				<s:property value="#rp.index+1"/>.<s:property value="reqProg"/>
//				&nbsp;完成<s:property value="percent"/>%
//				【<s:date name="createTime" format="yyyy-MM-dd"/>】。<br/>
//				</s:iterator>
				List<ReqProgress> progresses=bsRequest.getReqProgresses();
				if(progresses.size()!=0){
					System.out.println(j);
					taskIds="";
					for (int k = 0; k < progresses.size(); k++) {
						progress=progresses.get(k);
						taskIds+=k+1+".";
						if(progress.getReqProg()!=null&&!"".equals(progress.getReqProg())){
							taskIds+=progress.getReqProg();
						}
						taskIds+="。完成";
						if(progress.getPercent()!=null&&!"".equals(progress.getPercent())){
							taskIds+=progress.getPercent();
						}
						taskIds+="% [";
						if(progress.getCreateTime()!=null){
							taskIds+=DateUtil.dateToStr(progress.getCreateTime(), "yyyy-MM-dd");
						}
						taskIds+="]。";
					}
					label = new Label(j++, i + 1, taskIds);
					sheet.addCell(label);
				}else{
					label = new Label(j++, i + 1, "");
					sheet.addCell(label);
				}
				sheet.setRowView(0, 450);
				for (int k = 0; k < j; k++) {
					sheet.setColumnView(k, 25);
				}
			}
			book.write();
			book.close();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 导入execl
	 * @return
	 */
	public String into() {
		getAppDataTranferService().impDataFromExcel();
		return null;
	}

	public String findBsReqReport() {
		HttpServletRequest request = ApplicationContextUtil.getHttpServletRequest();
		int pageSize = PageNavUtil.getPageSize(request);
		if(flowReqReportForm==null){
			return findBsReqReport(pageSize);
		}
		if("2".equals(flowReqReportForm.getType())){
			return findBsReqReportNotGraph(pageSize);
		}else{
			
			return findBsReqReport(pageSize);
		}
	}
	/**
	 * 
	 * @param pageSize
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String findBsReqReport(int pageSize) {
		HttpServletRequest request = ApplicationContextUtil.getHttpServletRequest();
		FlowPvcDao flowPvcDao = getProcessPvcService().getFlowPvcDao();
		processList = flowPvcDao.findAllProcessList();
		List<String> shortDeptList=getAppBusinessReqService().getAppBusinessReqDao().getAllShortName();
		request.setAttribute("shortDeptList", shortDeptList);
		List<ProcessConfig> listProcessConfig=new ArrayList<ProcessConfig>();
		ProcessConfig processConfig=new ProcessConfig();
		
		for (int i = 0; i < processList.size(); i++) {
			processConfig=processList.get(i);
			if("1".equals(processConfig.getIsDefault())){
				listProcessConfig.add(processConfig);
			}
		}
		
		processList=listProcessConfig;
		if (flowReqReportForm != null) {
			AppBusinessReqDao appBusinessReqDao = getAppBusinessReqService().getAppBusinessReqDao();
			List<ProcessConfigDetail> processConfigDetails = flowPvcDao
					.findAllProcessDetail(Integer.parseInt(flowReqReportForm
							.getProcessId()));
			request.setAttribute("processConfigDetails", processConfigDetails);
			request.setAttribute("num", processConfigDetails.size());
			PageBean pageBean = getAppBusinessReqService().getAppBusinessReqDao().queryPageBsReqReportList(
							flowReqReportForm, PageNavUtil.getPageNo(request),pageSize);
			List<BusinessRequest> bsReqList = pageBean.getDataList();
			int total = processConfigDetails.size();
			if (bsReqList != null && bsReqList.size() > 0) {
				float fCount = 0;
				float tCount = 0;
				String nodeId = null;
				String nodeId2 = null;
				List<ProcTranRecord> procRecordList = null;
				String currentUser = null;
				String taskIds = "";
				int i = 0;
				for (BusinessRequest businessRequest : bsReqList) {
					// 取得流程运行中的任务记录
					procRecordList = flowPvcDao.findRunTaskList(businessRequest
							.getProcinstId());
					fCount = 0;
					tCount = 0;
					nodeId = null;
					nodeId2 = null;
					currentUser = "";
					taskIds = "";
					if (procRecordList != null && procRecordList.size() > 0) {
						for (ProcTranRecord procTranRecord : procRecordList) {
							currentUser += procTranRecord.getTransactor().getName()
									+ ",";
							taskIds += "'" + procTranRecord.getId() + "'" + ",";
							tCount = 1;
							nodeId = procTranRecord.getProcessStep().getId()
									.toString();
							for (ProcessConfigDetail processConfigDetail : processConfigDetails) {
								nodeId2 = processConfigDetail.getId()
										.toString();
								if (nodeId.equalsIgnoreCase(nodeId2)) {
									break;
								}
								tCount++;
							}
							if (fCount < tCount) {
								fCount = tCount;
							}
						}
						Integer num = getProcessPvcService().getFlowPvcDao()
								.getMaxOrderBy(businessRequest.getProcinstId());
						if (num == null) {
							num = 0;
						}
						businessRequest
								.setPercent((int) (((float) num / total) * 100));
						businessRequest.setCurrUser(currentUser);
						// 取得进展记录信息
						taskIds = taskIds.substring(0, taskIds.length() - 1);
						List reqProgressList = appBusinessReqDao
								.findReqProgressListByTaskIds(taskIds);
						businessRequest.setReqProgresses(reqProgressList);
					} else {
						Integer num = getProcessPvcService().getFlowPvcDao()
								.getMaxOrderBy(businessRequest.getProcinstId());
						if (num == null)
							num = 0;
						businessRequest.setPercent((int) (((float) num / total) * 100));
						businessRequest.setCurrUser("");
					}
					i++;
				}
			}
			request.setAttribute("reportList", bsReqList);
			String pageNaviStr = PageNavi.getPageNaviText(request, pageBean);
			request.setAttribute("pageNaviStr", pageNaviStr);
		}
		return SUCCESS;
	}
	/**
	 * 需求单进展报表 
	 * @return
	 */
	public String findBsReqReportNotGraph() {
		HttpServletRequest request = ApplicationContextUtil
		.getHttpServletRequest();
 		int pageSize = PageNavUtil.getPageSize(request);
		if(flowReqReportForm==null){
			return this.findBsReqReportNotGraph(pageSize);
		}
		if("1".equals(flowReqReportForm.getType())){
			return findBsReqReport(pageSize);
		}else{
			return this.findBsReqReportNotGraph(pageSize);
		}
	}
	/**
	 * 需求单报表，非图形
	 * @param pageSize
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String findBsReqReportNotGraph(int pageSize) {
		HttpServletRequest request = ApplicationContextUtil.getHttpServletRequest();
		FlowPvcDao flowPvcDao = getProcessPvcService().getFlowPvcDao();
		processList = flowPvcDao.findAllProcessList();
		List<String> shortDeptList=getAppBusinessReqService().getAppBusinessReqDao().getAllShortName();
		request.setAttribute("shortDeptList", shortDeptList);
		List<ProcessConfig> listProcessConfig=new ArrayList<ProcessConfig>();
		ProcessConfig processConfig=new ProcessConfig();
//		processConfig.setId(null);
//		processConfig.setName("请选择流程");
//		listProcessConfig.add(processConfig);
		for (int i = 0; i < processList.size(); i++) {
			processConfig=processList.get(i);
			if("1".equals(processConfig.getIsDefault())){
				listProcessConfig.add(processConfig);
			}
		}
		processList=listProcessConfig;
		List<String> typeList=new ArrayList<String>();
		List types=this.getAppBusinessReqService().getAppBusinessReqDao().getDaiTypes("");
		for (int i = 0; i < types.size(); i++) {
			Map<String, String> m=(Map<String, String>) types.get(i);
			typeList.add(m.get("dai_type"));
		}
		if (flowReqReportForm != null) {
			AppBusinessReqDao appBusinessReqDao = getAppBusinessReqService().getAppBusinessReqDao();
			PageBean pageBean = getAppBusinessReqService().getAppBusinessReqDao().queryPageBsReqReportList(flowReqReportForm, PageNavUtil.getPageNo(request),
							pageSize);
			List<BusinessRequest> bsReqList = pageBean.getDataList();
			if (bsReqList != null && bsReqList.size() > 0) {
				List<ProcTranRecord> procRecordList = null;
				String taskIds = "";
				int i = 0;
				for (BusinessRequest businessRequest : bsReqList) {
					// 取得流程运行中的任务记录
					procRecordList = flowPvcDao.findRunTaskList(businessRequest.getProcinstId());
					taskIds = "";
					if (procRecordList != null && procRecordList.size() > 0) {
						for (ProcTranRecord procTranRecord : procRecordList) {
							if("active".equals(procTranRecord.getState())){
								businessRequest.setCurrStatus(procTranRecord.getProcessStep().getName());
								businessRequest.setCurrUser(procTranRecord.getTransactor().getName());
							}
							taskIds += "'" + procTranRecord.getId() + "'" + ",";
						}
						// 取得进展记录信息
						taskIds = taskIds.substring(0, taskIds.length() - 1);
						List reqProgressList = appBusinessReqDao
								.findReqProgressListByTaskIds(taskIds);
						businessRequest.setReqProgresses(reqProgressList);
					} else {
						businessRequest.setCurrUser("");
						businessRequest.setCurrStatus("结束");
					}
					i++;
				}
			}
			request.setAttribute("reportList", bsReqList);
			String pageNaviStr = PageNavi.getPageNaviText(request, pageBean);
			request.setAttribute("pageNaviStr", pageNaviStr);
		}
		request.setAttribute("types", typeList);
		return "reqReportListNotGraph";
	}
	/**
	 * 需求单报表统计
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String sumBsReqReport(){
		HttpServletRequest request = ApplicationContextUtil.getHttpServletRequest();
		String str_type=request.getParameter("type");
		if(str_type==null){
			str_type="type";
		}
		List types=null;
		List<String> typeList=new ArrayList<String>();
		List<List<String>> sumList=null;
		List<List<String>> overList=null;
		List<String> totalList=new ArrayList<String>();
		if(flowReqReportForm!=null){
			if("101".equals(flowReqReportForm.getProcessId())){
				if("dai_type".equals(str_type)){
					types=this.getAppBusinessReqService().getAppBusinessReqDao().getDaiTypes("01");
				}else{
					types=this.getAppBusinessReqService().getAppBusinessReqDao().getTypes("01");
				}
			}else if("103".equals(flowReqReportForm.getProcessId())){
				if("dai_type".equals(str_type)){
					types=this.getAppBusinessReqService().getAppBusinessReqDao().getDaiTypes("02");
				}else{
					types=this.getAppBusinessReqService().getAppBusinessReqDao().getTypes("02");
				}
			}
			for (int i = 0; i < types.size(); i++) {
				Map<String, String> m=(Map<String, String>) types.get(i);
				typeList.add(m.get(str_type));
			}
			if("dai_type".equals(str_type)){
				sumList=getAppBusinessReqService().getAppBusinessReqDao().listSumBs(getSqlNotOver(types,flowReqReportForm.getProcessId(),str_type),types.size(),2);
				overList=getAppBusinessReqService().getAppBusinessReqDao().listOverBs(getSqlOver(types,flowReqReportForm.getProcessId(),str_type),types.size(),1);
			}else{
				sumList=getAppBusinessReqService().getAppBusinessReqDao().listSumBs(getSqlNotOver(types,flowReqReportForm.getProcessId(),str_type),types.size(),3);
				overList=getAppBusinessReqService().getAppBusinessReqDao().listOverBs(getSqlOver(types,flowReqReportForm.getProcessId(),str_type),types.size(),2);
			}
			List olist=overList.get(0);
			for (int i = 0; i < olist.size(); i++) {
				Integer total=0;
				for (int j = 0; j < sumList.size(); j++) {
					List<String> rows=sumList.get(j);
					total+=Integer.parseInt(rows.get(i+1));
				}
				totalList.add(total.toString());
			}
			List<String> totalList2=new ArrayList<String>();
			List list=overList.get(0);
			for (int i = 0; i < totalList.size(); i++) {
				Integer total=Integer.parseInt(totalList.get(i));
				Integer over=Integer.parseInt((String) list.get(i));
				total+=over;
				totalList2.add(total.toString());
			}
			totalList=totalList2;
		}
		request.setAttribute("types", typeList);
		request.setAttribute("sumList", sumList);
		request.setAttribute("totalList", totalList);
		request.setAttribute("overList", overList);
		FlowPvcDao flowPvcDao = getProcessPvcService().getFlowPvcDao();
		
		processList = flowPvcDao.findAllProcessList();
		List<ProcessConfig> listProcessConfig=new ArrayList<ProcessConfig>();
		for (int i = 0; i < processList.size(); i++) {
			ProcessConfig processConfig=processList.get(i);
			if("1".equals(processConfig.getIsDefault())){
				listProcessConfig.add(processConfig);
			}
		}
		processList=listProcessConfig;
		if("dai_type".equals(str_type)){
			return "sumReqRepor2";
		}else{
			return "sumReqRepor";
		}
		
	}
	@SuppressWarnings("unchecked")
	public String getSqlNotOver(List<Map<String, String>> types,String processId,String typeName){
		String sql="select t1.name";//,t1.order_by
		for (int i = 1; i < types.size()+1; i++) {
			sql+=",nvl(t.A"+i+",0)as A"+i;
		}
		if("type".equals(typeName)){
			sql+=",nvl(t.A0, 0) as A0";
		}
		sql+=",nvl(t.A00,0) as A00 from (select curr_process_config_detail,sum(taskcount) as A00";
		if("type".equals(typeName)){
			sql+=",sum(decode(type,null,taskCount,0)) A0";
		}
		for (int i = 1; i < types.size()+1; i++) {
			Map type=types.get(i-1);
			sql+=",sum(decode("+typeName+",'"+type.get(typeName)+"',taskCount,0)) A"+i;
		}
		sql+=" from("
		+"select t2.curr_process_config_detail,t3."+typeName+",count(t2.id) taskCount"
		+"  from t_bpm_transactprocessstatus t2,t_business_businessrequest t3"
		+" where   t2.procinst_id = t3.procinst_id"
		+"    and t2.curr_process_config="+processId
		+"   and t2.curr_process_config_detail is not null"
		+"	 and t2.end_time is null and t3.end>sysdate"
		+"   group by t2.curr_process_config_detail,t3."+typeName
		+" )"
		+" group by curr_process_config_detail"
		+")t ,t_bpm_processconfigdetail  t1"
		+" where  t1.id = t.curr_process_config_detail(+) "
		+" and t1.process_config="+processId
		+" and t1.order_by is not null order by t1.order_by";
		System.out.println(sql);
		return sql;
	}
	@SuppressWarnings("unchecked")
	public String getSqlOver(List<Map<String, String>> types,String processId,String typeName){
		String sql="select ";//,t1.order_by
		for (int i = 1; i < types.size()+1; i++) {
			sql+="nvl(sum(t.A"+i+"),0)as A"+i+",";
		}
		if("type".equals(typeName)){
			sql+="nvl(sum(t.A0),0) as A0,";
		}
		sql+="nvl(sum(t.A00),0) as A00 from (select curr_process_config_detail,sum(taskcount) as A00";
		if("type".equals(typeName)){
			sql+=",sum(decode(type,null,taskCount,0)) A0";
		}
		for (int i = 1; i < types.size()+1; i++) {
			Map type=types.get(i-1);
			sql+=",sum(decode("+typeName+",'"+type.get(typeName)+"',taskCount,0)) A"+i;
		}
		sql+=" from("
		+"select t2.curr_process_config_detail,t3."+typeName+",count(t2.id) taskCount"
		+"  from t_bpm_transactprocessstatus t2,t_business_businessrequest t3"
		+" where   t2.procinst_id = t3.procinst_id"
		+"    and t2.curr_process_config="+processId
		+"   and t2.curr_process_config_detail is not null"
		+"	 and t2.end_time is not null and t3.end>sysdate"
		+"   group by t2.curr_process_config_detail,t3."+typeName
		+" )"
		+" group by curr_process_config_detail"
		+")t ,t_bpm_processconfigdetail  t1"
		+" where  t1.id = t.curr_process_config_detail(+) "
		+" and t1.process_config="+processId
		+" and t1.order_by is not null order by t1.order_by";
		System.out.println(sql);
		return sql;
	}
	
	/**
	 * 导出需求单报表统计
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String outSumBsReq(){
		try {
			HttpServletRequest request = ApplicationContextUtil	.getHttpServletRequest();
			String str_type=request.getParameter("type");
			HttpServletResponse response = ApplicationContextUtil.getHttpServletResponse();
			response.setContentType("application/stream;charset=UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ URLEncoder.encode("需求单报表统计"+DateUtil.dateToStr(new Date(), "yyyy-MM-dd")+".xls", "UTF-8") + "\"");
			OutputStream os = response.getOutputStream();
			WritableWorkbook book = Workbook.createWorkbook(os);
			WritableSheet sheet = book.createSheet("Sheet_1", 0);
			WritableCellFormat cellFormat=new WritableCellFormat();
		    cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
			List types=null;
			List<String> typeList=new ArrayList<String>();
			List<List<String>> sumList=null;
			List<List<String>> overList=null;
			@SuppressWarnings("unused")
			List<String> totalList=new ArrayList<String>();
			Map<String,String> m=null;
			if(flowReqReportForm!=null){
				if("101".equals(flowReqReportForm.getProcessId())){
					if("dai_type".equals(str_type)){
						types=this.getAppBusinessReqService().getAppBusinessReqDao().getDaiTypes("01");
					}else{
						types=this.getAppBusinessReqService().getAppBusinessReqDao().getTypes("01");
					}					
				}else if("103".equals(flowReqReportForm.getProcessId())){
					if("dai_type".equals(str_type)){
						types=this.getAppBusinessReqService().getAppBusinessReqDao().getDaiTypes("02");
					}else{
						types=this.getAppBusinessReqService().getAppBusinessReqDao().getTypes("02");
					}	
				}
				for (int i = 0; i < types.size(); i++) {
					m=(Map<String, String>) types.get(i);
					typeList.add(m.get(str_type));
				}
				Label label = new Label(0, 0, "节点名称");
				sheet.addCell(label);
				for (int i = 0; i < types.size(); i++) {
					m=(Map<String, String>) types.get(i);
					label = new Label(i+1, 0, m.get(str_type));
					label.setCellFormat(cellFormat);
					sheet.addCell(label);
				}
				if("dai_type".equals(str_type)){
					label = new Label(types.size()+1, 0, "合计");
					label.setCellFormat(cellFormat);
					sheet.addCell(label);
					sumList=getAppBusinessReqService().getAppBusinessReqDao().listSumBs(getSqlNotOver(types,flowReqReportForm.getProcessId(),str_type),types.size(),2);
					overList=getAppBusinessReqService().getAppBusinessReqDao().listOverBs(getSqlOver(types,flowReqReportForm.getProcessId(),str_type),types.size(),1);
				}else{
					label = new Label(types.size()+1, 0, "其他");
					label.setCellFormat(cellFormat);
					label.setCellFormat(cellFormat);
					sheet.addCell(label);
					label = new Label(types.size()+2, 0, "合计");
					label.setCellFormat(cellFormat);
					label.setCellFormat(cellFormat);
					sheet.addCell(label);
					sumList=getAppBusinessReqService().getAppBusinessReqDao().listSumBs(getSqlNotOver(types,flowReqReportForm.getProcessId(),str_type),types.size(),3);
					overList=getAppBusinessReqService().getAppBusinessReqDao().listOverBs(getSqlOver(types,flowReqReportForm.getProcessId(),str_type),types.size(),2);
				}	
				label = new Label(0, 2, "已完成");
				sheet.addCell(label);
				List olist=overList.get(0);
				jxl.write.Number number = null;
				for (int k = 0; k < olist.size(); k++) {
					number = new jxl.write.Number(k+1, 2, Double.parseDouble((String) olist.get(k)) );   
				    number.setCellFormat(cellFormat);
					sheet.addCell(number);   
				}
				List list=null;
				for (int i = 0; i < sumList.size(); i++) {
					list=sumList.get(i);
					for (int j = 0; j < list.size(); j++) {
						if(j!=0){
							number = new jxl.write.Number(j, i+3, Double.parseDouble(list.get(j).toString()) );   
						    number.setCellFormat(cellFormat);
							sheet.addCell(number);
						}else{
							label = new Label(j, i+3, list.get(j).toString());
							sheet.addCell(label);
						}
					}
				}
				label = new Label(0, 1, "任务总数");
				sheet.addCell(label);
				for (int i = 0; i < olist.size(); i++) {
					Integer over= Integer.parseInt(olist.get(i).toString()) ;
					for (int k = 0; k < sumList.size(); k++) {
						list=sumList.get(k);
						over+=Integer.parseInt(list.get(i+1).toString());
					}
					number = new jxl.write.Number(i+1, 1, Double.parseDouble(over.toString()) );   
				    number.setCellFormat(cellFormat);
					sheet.addCell(number);
				}
				for (int i = 0; i < sumList.size()+3; i++) {
					sheet.setColumnView(i, 15);
				}
			}
			book.write();
			book.close();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	public ProcessPvcService getProcessPvcService() {
		return processPvcService;
	}

	public void setProcessPvcService(ProcessPvcService processPvcService) {
		this.processPvcService = processPvcService;
	}

	public TaskService getTaskService() {
		return taskService;
	}

	public void setTaskService(TaskService taskService) {
		this.taskService = taskService;
	}

	public AppBusinessReqService getAppBusinessReqService() {
		return appBusinessReqService;
	}

	public void setAppBusinessReqService(
			AppBusinessReqService appBusinessReqService) {
		this.appBusinessReqService = appBusinessReqService;
	}

	public FlowReqReportForm getFlowReqReportForm() {
		return flowReqReportForm;
	}

	public void setFlowReqReportForm(FlowReqReportForm flowReqReportForm) {
		this.flowReqReportForm = flowReqReportForm;
	}

	public List<ProcessConfig> getProcessList() {
		return processList;
	}

	public void setProcessList(List<ProcessConfig> processList) {
		this.processList = processList;
	}

	public AppDataTranferService getAppDataTranferService() {
		return appDataTranferService;
	}

	public void setAppDataTranferService(AppDataTranferService appDataTranferService) {
		this.appDataTranferService = appDataTranferService;
	}

}
